﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Cir.Factory;


namespace Dal.AcessoDal
{
    public class ConexaoDal
    {
        public SqlConnection SqlConn;
        SqlCommand SqlCmd;
        
        SqlDataAdapter SqlDa;
        SqlDataReader SqlDr;
        DataTable dt;
        DataSet ds;

        public SqlConnection AbrirConexao()
        {
            try
            {
                SqlConn = new SqlConnection();
                //SqlConn.ConnectionString = Cir.Factory.ConnectionFactory.getConnection();
                //SqlConn.ConnectionString = "Data Source=SIMETRIAS101;Initial Catalog=DBCIRMABERKLEY;User ID=SA;Password=";
                SqlConn.ConnectionString = ConfigurationSettings.AppSettings["ConnString"].ToString();

                if (SqlConn.State != ConnectionState.Open)
                {
                    SqlConn.Open();
                }
                return SqlConn;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private Boolean FecharConexao()
        {
            try
            {
                if (SqlConn.State == ConnectionState.Open)
                {
                    SqlConn.Close();
                }
                return true;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable RetornaDataTable(String strSQL)
        {

            SqlCmd = new SqlCommand();
            SqlDa = new SqlDataAdapter();
            ds = new DataSet();
            dt = new DataTable();

            try
            {
                AbrirConexao();
                
                SqlCmd.Connection = SqlConn;
                SqlCmd.CommandText = strSQL;
                

                SqlDa.SelectCommand = SqlCmd;
                SqlDa.Fill(ds);
                SqlDa.Dispose();

                ds.AcceptChanges();
                dt = ds.Tables[0];
                ds.Dispose();

                SqlConn.Close();

                SqlCmd.Dispose();

                SqlDa.Dispose();

                return dt;
            }
            catch (SqlException ex)
            {
                throw new Exception("Ocorreu um erro na Base de Dados. Ao Tentar Carregar o Grid" + " - " + ex.Message);
            }

            catch (Exception ex)
            {
                throw new Exception("Ocorreu um erro inesperado. Ao Tentar Carregar o Grid" + " - " + ex.Message);
            }
        }


        public SqlDataReader RetornaDataReader(String strSQL)
        {

            SqlCmd = new SqlCommand();

            try
            {
                AbrirConexao();
                SqlCmd.Connection = SqlConn;
                SqlCmd.CommandText = strSQL;

                SqlDr = SqlCmd.ExecuteReader();

                return SqlDr;
            }
            catch (SqlException ex)
            {
                throw new Exception("Ocorreu um erro na Base de Dados. Ao Tentar Carregar o Grid" + " - " + ex.Message);
            }

            catch (Exception ex)
            {
                throw new Exception("Ocorreu um erro inesperado. Ao Tentar Carregar o Grid" + " - " + ex.Message);
            }
        }


 
    }
}
